package com.cskaoyan.wordmemorize.service.impl;

import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.cskaoyan.wordmemorize.common.aspect.operate.OperateLog;
import com.cskaoyan.wordmemorize.common.constant.ResultCodeEnum;
import com.cskaoyan.wordmemorize.common.exception.BusinessException;
import com.cskaoyan.wordmemorize.converter.VocConverter;
import com.cskaoyan.wordmemorize.dao.entity.*;
import com.cskaoyan.wordmemorize.dao.mapper.*;
import com.cskaoyan.wordmemorize.dto.admin.*;
import com.cskaoyan.wordmemorize.dto.app.DisplayBookVocDTO;
import com.cskaoyan.wordmemorize.dto.app.DisplayChapterDTO;
import com.cskaoyan.wordmemorize.dto.app.UserBookStatisticsDTO;
import com.cskaoyan.wordmemorize.request.*;
import com.cskaoyan.wordmemorize.service.FileUploadService;
import com.cskaoyan.wordmemorize.service.VocService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.function.Function;
import java.util.stream.Collectors;

@Slf4j
@Service
public class VocServiceImpl implements VocService {

    @Autowired
    VocMapper vocMapper;

    @Autowired
    VocConverter vocConverter;

    @Autowired
    VocMeaningMapper vocMeaningMapper;

    @Autowired
    ExampleSentenceMapper exampleSentenceMapper;

    @Autowired
    VocChapBookMapper vocChapBookMapper;

    @Autowired
    VocChapterMapper vocChapterMapper;

    @Autowired
    VocCategoryMapper vocCategoryMapper;

    @Autowired
    VocBookMapper vocBookMapper;

    @Autowired
    FileUploadService fileUploadService;

    @Autowired
    UserVocRevMapper userVocRevMapper;

    @Autowired
    UserNotRememberMapper userNotRememberMapper;

    @Autowired
    UserFavoriteMapper userFavoriteMapper;

    @Autowired
    UserVocExampleSentenceMapper userVocExampleSentenceMapper;

    /**
     * 分页查询单词信息(物料管理-词汇管理)
     *
     * @param pageQuery
     * @return 包含单词信息的分页数据
     */
    @Override
    public PageDTO<VocDTO> getVocabularies(VocPageRequest pageQuery) {
        List<Long> vocIdList = null;
        // 需要先查关系表，获取vocId列表
        // 请求中可能会包含书籍ID或章节ID，需要从关系表中筛选符合条件的单词ID
        if ((pageQuery.getBookId() != null && !pageQuery.getBookId().trim().isEmpty()) ||
                (pageQuery.getChapterId() != null && !pageQuery.getChapterId().trim().isEmpty())) {
            // 查询单词与章节、书籍的关系
            LambdaQueryWrapper<VocChapBookDO> relWrapper = new LambdaQueryWrapper<>();
            // 只查询未被删除的关系
            relWrapper.eq(VocChapBookDO::getIsDeleted, 0);
            if (pageQuery.getBookId() != null && !pageQuery.getBookId().trim().isEmpty()) {
                // 请求中包含书籍ID，添加书籍ID的查询条件
                relWrapper.eq(VocChapBookDO::getBookId, pageQuery.getBookId());
            }
            if (pageQuery.getChapterId() != null && !pageQuery.getChapterId().trim().isEmpty()) {
                // 请求中包含章节ID，添加章节ID的查询条件
                relWrapper.eq(VocChapBookDO::getChapterId, pageQuery.getChapterId());
            }
            List<VocChapBookDO> rels = vocChapBookMapper.selectList(relWrapper);

            vocIdList = new ArrayList<>();
            // 遍历关系记录列表，将其中的单词ID添加到单词ID列表中
            for (VocChapBookDO rel : rels) {
                vocIdList.add(rel.getVocId());
            }
            if (vocIdList.isEmpty()) {
                // 若单词ID列表为空，说明没有符合条件的单词，直接返回空的分页数据
                return vocConverter.vocList2Page(new ArrayList<>(), 0L);
            }
        }
        // 查询单词表
        LambdaQueryWrapper<VocDO> wrapper = new LambdaQueryWrapper<>();
        // 只查询未被删除的单词
        wrapper.eq(VocDO::getIsDeleted, 0);

        if (vocIdList != null) {
            // 单词ID列表不为空，(查询的单词需要在单词列表中 in方法)
            wrapper.in(VocDO::getId, vocIdList);
        }
        if (pageQuery.getKeyword() != null && !pageQuery.getKeyword().trim().isEmpty()) {
            // 请求中包含关键词，添加单词内容的模糊查询
            wrapper.like(VocDO::getContent, pageQuery.getKeyword());
        }
        // 单词一般按照a-b-c这样排序,所以用字典序排序(即直接用单词本身来排序)
        wrapper.orderByAsc(VocDO::getContent);

        List<VocDO> vocList; // 单词列表

        long total; // 总条数

        if (pageQuery.getPageNum() == null || pageQuery.getCount() == null) {
            // 分页参数为空，查询所有
            vocList = vocMapper.selectList(wrapper);
            total = vocList.size();
        } else {
            Page<VocDO> page = new Page<>(pageQuery.getPageNum(), pageQuery.getCount());
            Page<VocDO> result = vocMapper.selectPage(page, wrapper);
            vocList = result.getRecords();
            total = result.getTotal();
        }
        // 释义和例句
        for (VocDO voc : vocList) {
            // 释义
            List<VocMeaningDO> meanings = vocMeaningMapper.selectList(
                    new LambdaQueryWrapper<VocMeaningDO>().eq(VocMeaningDO::getVocId, voc.getId()));
            voc.setInterpretations(meanings);
            // 例句
            List<ExampleSentenceDO> examples = exampleSentenceMapper.selectList(
                    new LambdaQueryWrapper<ExampleSentenceDO>().eq(ExampleSentenceDO::getVocId, voc.getId()));
            voc.setExamples(examples);
        }
        return vocConverter.vocList2Page(vocList, total);
    }

    /**
     * 添加单词
     *
     * @param command
     */
    @Override
    @OperateLog(dataType = "单词", operateType = 1, description = "新增单词")
    public void addVoc(VocCommand command) {

        // 判断要添加的单词是否存在
        LambdaQueryWrapper<VocDO> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(VocDO::getContent, command.getContent());

        VocDO vocDO = vocMapper.selectOne(queryWrapper);
        if (vocDO != null) {
            throw new BusinessException(ResultCodeEnum.VOC_EXISTED);
        }

        // 保存单词
        VocDO voc = vocConverter.command2VocDO(command);
        // 保存基本的单词数据
        vocMapper.insert(voc);

        // 保存单词释义
        List<VocMeaningCommand> interpretations = command.getInterpretations();
        List<VocMeaningDO> vocMeaningDOs = vocConverter.commands2VocMeaningDOs(interpretations);

        if (!CollectionUtils.isEmpty(vocMeaningDOs)) {

            vocMeaningDOs.forEach(vocMeaningDO -> {

                // 设置释义多对应的单词id
                vocMeaningDO.setVocId(voc.getId());
                vocMeaningMapper.insert(vocMeaningDO);
            });
        }

        // 保存例句
        List<ExampleSentenceCommand> examples = command.getExamples();
        List<ExampleSentenceDO> exampleSentenceDOs = vocConverter.commands2ExampleSentenceDOs(examples);
        if (!CollectionUtils.isEmpty(exampleSentenceDOs)) {
            exampleSentenceDOs.forEach(exampleSentenceDO -> {

                // 设置例句对应的单词id
                exampleSentenceDO.setVocId(voc.getId());
                exampleSentenceMapper.insert(exampleSentenceDO);
            });
        }

        // 保存单词-章节-书籍关系
        List<VocChapBookCommand> relations = command.getRelations();
        List<VocChapBookDO> vocChapBookDOs = vocConverter.commands2VocChapBookDOs(relations);
        if (!CollectionUtils.isEmpty(vocChapBookDOs)) {
            vocChapBookDOs.forEach(vocChapBookDO -> {

                // 设置单词id
                vocChapBookDO.setVocId(voc.getId());
                vocChapBookMapper.insert(vocChapBookDO);

                // 修改章节中包含的单词数量
                if (vocChapBookDO.getChapterId() != null) {

                    LambdaUpdateWrapper<VocChapterDO> chapterWrapper = new LambdaUpdateWrapper<>();
                    chapterWrapper.eq(VocChapterDO::getId, vocChapBookDO.getChapterId())
                            .setSql("voc_count = voc_count + 1");
                    vocChapterMapper.update(null, chapterWrapper);

                }

                // 书籍中包含的单词数量
                LambdaUpdateWrapper<VocBookDO> vocBookWrapper = new LambdaUpdateWrapper<>();
                vocBookWrapper.eq(VocBookDO::getId, vocChapBookDO.getBookId())
                        .setSql("voc_count = voc_count + 1");

                vocBookMapper.update(null, vocBookWrapper);
            });
        }

    }

    /**
     * 修改单词
     *
     * @param command
     */
    @Override
    @Transactional
    @OperateLog(dataType = "单词", operateType = 4, description = "修改单词")
    public void updateVoc(VocCommand command) {
        // 检查单词是否在数据库中存在,只有单词存在才能修改
        LambdaQueryWrapper<VocDO> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(VocDO::getId, command.getId());

        VocDO vocDO = vocMapper.selectOne(queryWrapper);
        if (vocDO == null) {
            throw new BusinessException(ResultCodeEnum.VOC_NOT_EXISTED);
        }

        // 更新单词基本信息
        VocDO voc = vocConverter.command2VocDO(command);
        vocMapper.updateById(voc);

        // 处理释义
        List<VocMeaningCommand> interpretations = command.getInterpretations();
        if (!CollectionUtils.isEmpty(interpretations)) {
            for (VocMeaningCommand meaning : interpretations) {
                VocMeaningDO vocMeaningDO = vocConverter.command2VocMeaningDO(meaning);
                vocMeaningDO.setVocId(voc.getId());

                if (meaning.getId() == null) {
                    // 若释义 ID 为空，说明是新增释义，执行插入操作
                    vocMeaningMapper.insert(vocMeaningDO);
                } else {
                    // 若释义 ID 不为空，说明是更新释义，执行更新操作
                    vocMeaningMapper.updateById(vocMeaningDO);
                }
            }
        }

        // 处理例句
        List<ExampleSentenceCommand> examples = command.getExamples();
        if (!CollectionUtils.isEmpty(examples)) {
            for (ExampleSentenceCommand example : examples) {
                ExampleSentenceDO exampleSentenceDO = vocConverter.command2ExampleSentenceDO(example);
                exampleSentenceDO.setVocId(voc.getId());
                if (example.getId() == null) {
                    // 若例句 ID 为空，说明是新增例句，执行插入操作
                    exampleSentenceMapper.insert(exampleSentenceDO);
                } else {
                    // 若例句 ID 不为空，说明是更新例句，执行更新操作
                    exampleSentenceMapper.updateById(exampleSentenceDO);
                }
            }
        }

        // 处理单词-章节-书籍关系
        // 查询旧关系
        List<VocChapBookDO> oldRelations = vocChapBookMapper.selectList(
                new LambdaQueryWrapper<VocChapBookDO>().eq(VocChapBookDO::getVocId, voc.getId()));
        // 删除旧关系并减数量
        if (!CollectionUtils.isEmpty(oldRelations)) {
            for (VocChapBookDO old : oldRelations) {
                vocChapBookMapper.deleteById(old.getId());
                // 章节数量减1
                if (old.getChapterId() != null) {
                    vocChapterMapper.update(null, new LambdaUpdateWrapper<VocChapterDO>()
                            .eq(VocChapterDO::getId, old.getChapterId())
                            .setSql("voc_count = voc_count - 1"));
                }
                // 书籍数量减1
                vocBookMapper.update(null, new LambdaUpdateWrapper<VocBookDO>()
                        .eq(VocBookDO::getId, old.getBookId())
                        .setSql("voc_count = voc_count - 1"));
            }
        }
        // 插入新关系并加数量
        List<VocChapBookCommand> relations = command.getRelations();
        List<VocChapBookDO> vocChapBookDOs = vocConverter.commands2VocChapBookDOs(relations);
        if (!CollectionUtils.isEmpty(vocChapBookDOs)) {
            for (VocChapBookDO vocChapBookDO : vocChapBookDOs) {
                vocChapBookDO.setVocId(voc.getId());
                // 判断关系是否存在（不管isDeleted）
                LambdaQueryWrapper<VocChapBookDO> relWrapper = new LambdaQueryWrapper<>();
                relWrapper.eq(VocChapBookDO::getBookId, vocChapBookDO.getBookId())
                        .eq(VocChapBookDO::getChapterId, vocChapBookDO.getChapterId())
                        .eq(VocChapBookDO::getVocId, vocChapBookDO.getVocId());
                VocChapBookDO exist = vocChapBookMapper.selectOne(relWrapper);
                if (exist != null) {
                    if (exist.getIsDeleted() != null && exist.getIsDeleted() == 1) {
                        // 逻辑删除，恢复
                        exist.setIsDeleted(0);

                        vocChapBookMapper.updateById(exist);
                        // 数量加1
                        if (vocChapBookDO.getChapterId() != null) {
                            vocChapterMapper.update(null, new LambdaUpdateWrapper<VocChapterDO>()
                                    .eq(VocChapterDO::getId, vocChapBookDO.getChapterId())
                                    .setSql("voc_count = voc_count + 1"));
                        }
                        vocBookMapper.update(null, new LambdaUpdateWrapper<VocBookDO>()
                                .eq(VocBookDO::getId, vocChapBookDO.getBookId())
                                .setSql("voc_count = voc_count + 1"));
                    }
                    // 已存在且未删除，不做插入
                    continue;
                } else {
                    // 不存在，插入新关系
                    vocChapBookMapper.insert(vocChapBookDO);
                    if (vocChapBookDO.getChapterId() != null) {
                        vocChapterMapper.update(null, new LambdaUpdateWrapper<VocChapterDO>()
                                .eq(VocChapterDO::getId, vocChapBookDO.getChapterId())
                                .setSql("voc_count = voc_count + 1"));
                    }
                    vocBookMapper.update(null, new LambdaUpdateWrapper<VocBookDO>()
                            .eq(VocBookDO::getId, vocChapBookDO.getBookId())
                            .setSql("voc_count = voc_count + 1"));
                }
            }
        }
    }

    /**
     * 删除指定单词
     *
     * @param vocId
     * @return
     */
    @Override
    @Transactional
    @OperateLog(dataType = "单词", operateType = 2, description = "删除单词", logReturnValue = true)
    public DeleteVocOperateDTO deleteVocabulary(Long vocId) {
        VocDO voc = vocMapper.selectById(vocId);
        if (voc == null) {
            return null;
        }
        // 删除与书籍-章节的所有关系，维护数量
        List<VocChapBookDO> relations = vocChapBookMapper.selectList(
                new LambdaQueryWrapper<VocChapBookDO>().eq(VocChapBookDO::getVocId, vocId));
        if (!CollectionUtils.isEmpty(relations)) {
            for (VocChapBookDO relation : relations) {
                vocChapBookMapper.deleteById(relation.getId());
                if (relation.getChapterId() != null) {
                    vocChapterMapper.update(null, new LambdaUpdateWrapper<VocChapterDO>()
                            .eq(VocChapterDO::getId, relation.getChapterId())
                            .setSql("voc_count = voc_count - 1"));
                }
                vocBookMapper.update(null, new LambdaUpdateWrapper<VocBookDO>()
                        .eq(VocBookDO::getId, relation.getBookId())
                        .setSql("voc_count = voc_count - 1"));
            }
        }
        // 删除释义
        vocMeaningMapper.delete(new LambdaQueryWrapper<VocMeaningDO>().eq(VocMeaningDO::getVocId, vocId));
        // 删除例句
        exampleSentenceMapper
                .delete(new LambdaQueryWrapper<ExampleSentenceDO>().eq(ExampleSentenceDO::getVocId, vocId));
        // 删除单词本身
        vocMapper.deleteById(vocId);
        // 构造删除的DTO对象
        DeleteVocOperateDTO dto = new DeleteVocOperateDTO();
        dto.setId(vocId);
        dto.setContent(voc.getContent());
        return dto;
    }

    /**
     * 批量删除单词
     *
     * @param command
     * @return
     */
    @Override
    @Transactional
    @OperateLog(dataType = "单词", operateType = 3, description = "批量删除单词", logReturnValue = true)
    public List<DeleteVocOperateDTO> deleteVocabularies(BatchDeleteCommand command) {
        List<String> ids = command.getIds();
        if (CollectionUtils.isEmpty(ids)) {
            return null;
        }

        List<DeleteVocOperateDTO> result = new ArrayList<>();
        for (String idStr : ids) {
            Long vocId;
            try {
                vocId = Long.valueOf(idStr);
            } catch (NumberFormatException e) {
                continue;
            }
            DeleteVocOperateDTO dto = deleteVocabulary(vocId);
            if (dto != null) {
                result.add(dto);
            }
        }
        return result;
    }

    /**
     * 获取单词与章节-书籍的关系
     *
     * @param vocId
     * @return
     */
    @Override
    public List<VocChapBookDTO> getVocRelations(Long vocId) {
        // 查询所有关系
        List<VocChapBookDO> relations = vocChapBookMapper.selectList(
                new LambdaQueryWrapper<VocChapBookDO>().eq(VocChapBookDO::getVocId, vocId));
        if (CollectionUtils.isEmpty(relations)) {
            return new ArrayList<>();
        }
        // 补全DTO信息
        for (VocChapBookDO relation : relations) {
            // 章节名
            if (relation.getChapterId() != null) {
                VocChapterDO chapter = vocChapterMapper.selectById(relation.getChapterId());
                if (chapter != null) {
                    relation.setChapterName(chapter.getChapterName());
                    relation.setBookId(chapter.getBookId());
                    relation.setBookName(chapter.getBookName());
                }
            }
            // 书籍名
            if (relation.getBookId() != null && (relation.getBookName() == null || relation.getBookName().isEmpty())) {
                VocBookDO book = vocBookMapper.selectById(relation.getBookId());
                if (book != null) {
                    relation.setBookName(book.getName());
                    relation.setCategoryId(book.getBcId());
                }
            }
            // 类目名
            if (relation.getCategoryId() != null) {
                VocCategoryDO category = vocCategoryMapper.selectById(relation.getCategoryId());
                if (category != null) {
                    relation.setCategoryName(category.getCategoryName());
                }
            }
        }
        return vocConverter.vocChapBookDOs2DTOs(relations);
    }

    /**
     * APP端获取无章节的书籍单词列表
     *
     * @param pageRequest
     * @return
     */
    @Override
    public PageDTO<DisplayBookVocDTO> getDisplayBookVocsInfo(AppVocPageRequest pageRequest) {
        Long userId = Long.valueOf(pageRequest.getUserId());
        Long bookId = Long.valueOf(pageRequest.getBookId());
        Integer pageNum = pageRequest.getPageNum();
        Integer count = pageRequest.getCount();

        // 查询书籍中所有无章节的单词关系
        LambdaQueryWrapper<VocChapBookDO> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(VocChapBookDO::getBookId, bookId)
                .isNull(VocChapBookDO::getChapterId)
                .eq(VocChapBookDO::getIsDeleted, 0);
        List<VocChapBookDO> vocChapBookList = vocChapBookMapper.selectList(wrapper);
        if (CollectionUtils.isEmpty(vocChapBookList)) {
            return vocConverter.displayBookVocList2Page(new ArrayList<>(), 0L);
        }
        // 获取单词ID列表
        List<Long> vocIds = vocChapBookList.stream().map(VocChapBookDO::getVocId).collect(Collectors.toList());
        // 查询单词详细信息
        LambdaQueryWrapper<VocDO> vocWrapper = new LambdaQueryWrapper<>();
        vocWrapper.in(VocDO::getId, vocIds).eq(VocDO::getIsDeleted, 0);
        List<VocDO> vocList = vocMapper.selectList(vocWrapper);
        // 查询用户复习/不背状态
        Map<Long, UserVocRevDO> revMap = userVocRevMapper
                .selectList(new LambdaQueryWrapper<UserVocRevDO>().eq(UserVocRevDO::getUserId, userId)
                        .in(UserVocRevDO::getVocId, vocIds))
                .stream().collect(Collectors.toMap(UserVocRevDO::getVocId, Function.identity(), (a, b) -> b));
        Map<Long, UserNotRememberDO> notRememberMap = userNotRememberMapper
                .selectList(new LambdaQueryWrapper<UserNotRememberDO>().eq(UserNotRememberDO::getUserId, userId)
                        .in(UserNotRememberDO::getVocId, vocIds))
                .stream().collect(Collectors.toMap(UserNotRememberDO::getVocId, Function.identity()));
        // 设置单词状态
        for (VocDO voc : vocList) {
            voc.setReviewed(revMap.containsKey(voc.getId()) ? 1 : 0);
            voc.setNotRemember(notRememberMap.containsKey(voc.getId()) ? 1 : 0);
            voc.setFavorite(0);
        }
        // 分页处理：参数为null或为0则全查，否则分页
        long total = vocList.size();
        List<VocDO> pageList = vocList;

        /*
         * if (pageNum != null && count != null && pageNum > 0 && count > 0) {
         * int start = (pageNum - 1) * count;
         * int end = Math.min(start + count, vocList.size());
         * if (start < vocList.size()) {
         * pageList = vocList.subList(start, end);
         * } else {
         * pageList = new ArrayList<>();
         * }
         * }
         */

        List<DisplayBookVocDTO> dtoList = vocConverter.vocDos2DisplayChapterVocDTOs(pageList);
        PageDTO<DisplayBookVocDTO> pageDTO = new PageDTO<>();
        pageDTO.setPageList(dtoList);
        pageDTO.setTotalCount(total);

        return pageDTO;
    }

    /**
     * APP端获取有章节的书籍单词列表
     *
     * @param pageRequest
     * @return
     */
    @Override
    public PageDTO<DisplayChapterDTO> getDisplayBookChapterVocsInfo(AppVocPageRequest pageRequest) {
        Long userId = Long.valueOf(pageRequest.getUserId());
        Long bookId = Long.valueOf(pageRequest.getBookId());
        Integer pageNum = pageRequest.getPageNum();
        Integer count = pageRequest.getCount();
        // 查询书籍中所有章节
        LambdaQueryWrapper<VocChapterDO> chapterWrapper = new LambdaQueryWrapper<>();
        chapterWrapper.eq(VocChapterDO::getBookId, bookId)
                .eq(VocChapterDO::getIsDeleted, 0)
                .orderByAsc(VocChapterDO::getSortBy);
        List<VocChapterDO> chapterList = vocChapterMapper.selectList(chapterWrapper);
        if (CollectionUtils.isEmpty(chapterList)) {
            return vocConverter.displayChapterVocList2Page(new ArrayList<>(), 0L);
        }
        // 查询每个章节下的单词
        for (VocChapterDO chapter : chapterList) {
            //
            LambdaQueryWrapper<VocChapBookDO> vocWrapper = new LambdaQueryWrapper<>();
            vocWrapper.eq(VocChapBookDO::getChapterId, chapter.getId()) // 章节ID
                    .eq(VocChapBookDO::getIsDeleted, 0); // 未删除
            List<VocChapBookDO> vocChapBookList = vocChapBookMapper.selectList(vocWrapper);
            if (!CollectionUtils.isEmpty(vocChapBookList)) {
                // 章节下的单词ID列表
                List<Long> vocIds = vocChapBookList.stream().map(VocChapBookDO::getVocId).collect(Collectors.toList());
                Map<Long, UserVocRevDO> revMap = userVocRevMapper
                        .selectList(new LambdaQueryWrapper<UserVocRevDO>()
                                .eq(UserVocRevDO::getUserId, userId) // 用户ID
                                .in(UserVocRevDO::getVocId, vocIds))
                        .stream().collect(Collectors.toMap(UserVocRevDO::getVocId, Function.identity(), (a, b) -> b));
                Map<Long, UserNotRememberDO> notRememberMap = userNotRememberMapper
                        .selectList(new LambdaQueryWrapper<UserNotRememberDO>().eq(UserNotRememberDO::getUserId, userId)
                                .in(UserNotRememberDO::getVocId, vocIds))
                        .stream().collect(Collectors.toMap(UserNotRememberDO::getVocId, Function.identity()));
                chapter.setVocCount(vocIds.size());
                List<VocDO> vocs = new ArrayList<>();
                for (VocChapBookDO vocChapBook : vocChapBookList) {
                    VocDO voc = new VocDO();
                    voc.setId(vocChapBook.getVocId());
                    voc.setContent(vocChapBook.getVocName());
                    voc.setReviewed(revMap.containsKey(vocChapBook.getVocId()) ? 1 : 0);
                    voc.setNotRemember(notRememberMap.containsKey(vocChapBook.getVocId()) ? 1 : 0);
                    voc.setFavorite(0);
                    vocs.add(voc);
                }
                chapter.setVocs(vocs);
            } else {
                chapter.setVocCount(0);
                chapter.setVocs(new ArrayList<>());
            }
        }
        // 分页处理：参数为null或为0则全查，否则分页
        long total = chapterList.size();
        List<VocChapterDO> pageList = chapterList;
        /*
         * if (pageNum != null && count != null && pageNum > 0 && count > 0) {
         * int start = (pageNum - 1) * count;
         * int end = Math.min(start + count, chapterList.size());
         * if (start < chapterList.size()) {
         * pageList = chapterList.subList(start, end);
         * } else {
         * pageList = new ArrayList<>();
         * }
         * }
         */
        return vocConverter.displayChapterVocList2Page(pageList, total);
    }

    /**
     * APP端获取单词详情
     *
     * @param vocId  单词ID
     * @param userId 用户ID
     * @return 单词详情DTO
     */
    /**
     * APP端获取单词详情，包含单词、音标、发音、释义、例句、用户自建例句、复习/不背/收藏状态
     */
    @Override
    public VocDTO getVocDetail(Long vocId, Long userId) {
        // 查询单词基本信息
        VocDO vocDO = vocMapper.selectById(vocId);
        if (vocDO == null) {
            throw new BusinessException(ResultCodeEnum.VOC_NOT_EXISTED);
        }

        // 查询单词释义
        List<VocMeaningDO> meanings = vocMeaningMapper.selectList(
                new LambdaQueryWrapper<VocMeaningDO>().eq(VocMeaningDO::getVocId, vocId));
        vocDO.setInterpretations(meanings);

        // 查询单词例句
        List<ExampleSentenceDO> examples = exampleSentenceMapper.selectList(
                new LambdaQueryWrapper<ExampleSentenceDO>().eq(ExampleSentenceDO::getVocId, vocId));
        vocDO.setExamples(examples);

        // 查询用户自建例句（user_voc_sentence表，未删除） (应该需要鉴权)
        List<UserVocExampleSentenceDO> userExamples = userVocExampleSentenceMapper.selectList(
                new LambdaQueryWrapper<UserVocExampleSentenceDO>()
                        .eq(UserVocExampleSentenceDO::getUserId, userId)
                        .eq(UserVocExampleSentenceDO::getVocId, vocId)
                        .eq(UserVocExampleSentenceDO::getIsDeleted, 0));
        vocDO.setUserExamples(userExamples);

        // 查询是否已选中复习
        int reviewed = userVocRevMapper.selectCount(
                new LambdaQueryWrapper<UserVocRevDO>()
                        .eq(UserVocRevDO::getUserId, userId)
                        .eq(UserVocRevDO::getVocId, vocId)
                        .eq(UserVocRevDO::getIsDeleted, 0)) > 0 ? 1 : 0;
        vocDO.setReviewed(reviewed);

        // 查询是否不背
        int notRemember = userNotRememberMapper.selectCount(
                new LambdaQueryWrapper<UserNotRememberDO>()
                        .eq(UserNotRememberDO::getUserId, userId)
                        .eq(UserNotRememberDO::getVocId, vocId)
                        .eq(UserNotRememberDO::getIsDeleted, 0)) > 0 ? 1 : 0;
        vocDO.setNotRemember(notRemember);

        // 查询是否收藏
        int favorite = userFavoriteMapper.selectCount(
                new LambdaQueryWrapper<UserFavoriteDO>()
                        .eq(UserFavoriteDO::getUserId, userId)
                        .eq(UserFavoriteDO::getVocId, vocId)
                        .eq(UserFavoriteDO::getIsDeleted, 0)
                        .eq(UserFavoriteDO::getSelected, 1) // 是否收藏
        ) > 0 ? 1 : 0;
        vocDO.setFavorite(favorite);

        // 转换为DTO并返回
        return vocConverter.vocDO2DTO(vocDO);
    }

    @Override
    public UserBookStatisticsDTO getUserChooseCountInBook(Long userId, Long bookId) {
        return null;
    }

    /**
     * 校验Excel文件
     *
     * @param file
     * @throws IOException
     */
    @Override
    @Transactional(readOnly = true)
    public void vocExcelValidate(MultipartFile file) throws IOException {
        VocExcelListener listener = new VocExcelListener();
        EasyExcel.read(file.getInputStream(), VocExcelDTO.class, listener).sheet().doRead();

        // 校验数据
        List<VocExcelDTO> dataList = listener.getDataList();
        if (CollectionUtils.isEmpty(dataList)) {
            throw new BusinessException(ResultCodeEnum.VOC_EXCEL_PARSE_ERROR);
        }

        // 检查重复单词
        for (int i = 0; i < dataList.size(); i++) {
            VocExcelDTO dto = dataList.get(i);
            LambdaQueryWrapper<VocDO> queryWrapper = new LambdaQueryWrapper<>();
            queryWrapper.eq(VocDO::getContent, dto.getContent());
            if (vocMapper.selectCount(queryWrapper) > 0) {
                throw new BusinessException(ResultCodeEnum.VOC_EXISTED);
            }
        }
        // 2. 书籍校验
        // 2.1 提取所有书籍名称（去重）
        Set<String> bookNameSet = dataList.stream()
                .map(VocExcelDTO::getBookInfos)
                .filter(bookInfo -> bookInfo != null && !bookInfo.trim().isEmpty())
                .map(bookInfo -> {
                    String[] parts = bookInfo.split("#", 2);
                    return parts[0].trim(); // 提取书籍名称（格式：书籍#章节）
                })
                .filter(bookName -> !bookName.isEmpty())
                .collect(Collectors.toSet());

        if (!bookNameSet.isEmpty()) {
            // 2.2 查询数据库中存在的书籍
            LambdaQueryWrapper<VocBookDO> bookQuery = new LambdaQueryWrapper<>();
            bookQuery.in(VocBookDO::getName, bookNameSet);
            List<VocBookDO> existingBooks = vocBookMapper.selectList(bookQuery);

            // 2.3 提取存在的书籍名称
            Set<String> existingBookNames = existingBooks.stream()
                    .map(VocBookDO::getName)
                    .collect(Collectors.toSet());

            // 2.4 检查是否有不存在的书籍
            for (String bookName : bookNameSet) {
                if (!existingBookNames.contains(bookName)) {
                    throw new BusinessException(ResultCodeEnum.BOOK_OFFLINE);
                }
            }
        }

        // 3. 章节校验
        // 3.1 提取所有（书籍+章节）组合（去重）
        Set<String> bookChapterSet = dataList.stream()
                .map(VocExcelDTO::getBookInfos)
                .filter(bookInfo -> bookInfo != null && !bookInfo.trim().isEmpty())
                .map(bookInfo -> {
                    String[] parts = bookInfo.split("#", 2);
                    if (parts.length < 2) {
                        return null; // 跳过无章节的记录
                    }
                    String bookName = parts[0].trim();
                    String chapterName = parts[1].trim();
                    return bookName + "#" + chapterName; // 组合键：书籍#章节
                })
                .filter(combinedKey -> combinedKey != null && !combinedKey.isEmpty())
                .collect(Collectors.toSet());

        if (!bookChapterSet.isEmpty()) {
            // 3.2 批量查询书籍ID（用于关联章节）
            Set<String> allBooksInChapters = bookChapterSet.stream()
                    .map(key -> key.split("#")[0])
                    .collect(Collectors.toSet());

            LambdaQueryWrapper<VocBookDO> bookIdQuery = new LambdaQueryWrapper<>();
            bookIdQuery.in(VocBookDO::getName, allBooksInChapters)
                    .select(VocBookDO::getId, VocBookDO::getName);
            Map<String, Long> bookNameToIdMap = vocBookMapper.selectList(bookIdQuery).stream()
                    .collect(Collectors.toMap(VocBookDO::getName, VocBookDO::getId));

            // 3.3 检查每个章节是否存在
            for (String combinedKey : bookChapterSet) {
                String[] parts = combinedKey.split("#", 2);
                String bookName = parts[0];
                String chapterName = parts[1];

                Long bookId = bookNameToIdMap.get(bookName);
                if (bookId == null) {
                    throw new BusinessException(ResultCodeEnum.BOOK_OFFLINE); // 理论上不会触发（已在书籍校验通过）
                }

                // 3.4 查询章节是否存在
                LambdaQueryWrapper<VocChapterDO> chapterQuery = new LambdaQueryWrapper<>();
                chapterQuery.eq(VocChapterDO::getBookId, bookId)
                        .eq(VocChapterDO::getChapterName, chapterName)
                        .eq(VocChapterDO::getIsDeleted, 0);

                long chapterCount = vocChapterMapper.selectCount(chapterQuery);
                if (chapterCount == 0) {
                    throw new BusinessException(ResultCodeEnum.CHAPTER_NOT_EXISTED);
                }
            }
        }
    }

    /**
     * 从Excel文件导入单词
     *
     * @param file
     * @param userId
     * @return
     * @throws IOException
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    @OperateLog(dataType = "单词", operateType = 6, description = "Excel导入单词", logReturnValue = true)
    public ExcelAddOperateDTO addVocExcel(MultipartFile file, Long userId) throws IOException {
        VocExcelListener listener = new VocExcelListener();
        EasyExcel.read(file.getInputStream(), VocExcelDTO.class, listener).sheet().doRead();

        List<VocExcelDTO> dataList = listener.getDataList();
        int successCount = 0;

        for (VocExcelDTO dto : dataList) {
            try {
                saveVocFromExcel(dto);
                successCount++;
            } catch (Exception e) {
                // 记录错误日志
                e.printStackTrace();
            }
        }

        return new ExcelAddOperateDTO();
    }

    private void saveVocFromExcel(VocExcelDTO dto) {
        // 1. 检查单词是否已存在
        LambdaQueryWrapper<VocDO> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(VocDO::getContent, dto.getContent());
        if (vocMapper.selectCount(queryWrapper) > 0) {
            throw new BusinessException(ResultCodeEnum.VOC_EXISTED);
        }

        // 2. 保存单词基本信息
        VocDO vocDO = vocConverter.vocExcelDTO2VocDO(dto);
        vocMapper.insert(vocDO);
        Long vocId = vocDO.getId();

        // 3. 保存释义（使用转换器的命令转换方法）
        List<VocMeaningCommand> meaningCommands = parseMeaningCommands(dto.getInterpretations());
        List<VocMeaningDO> meanings = vocConverter.commands2VocMeaningDOs(meaningCommands);
        if (!CollectionUtils.isEmpty(meanings)) {
            for (VocMeaningDO meaning : meanings) {
                meaning.setVocId(vocId);
                vocMeaningMapper.insert(meaning);
            }
        }

        // 4. 保存例句（使用转换器的命令转换方法）
        List<ExampleSentenceCommand> exampleCommands = parseExampleCommands(dto.getExampleSentences());
        List<ExampleSentenceDO> examples = vocConverter.commands2ExampleSentenceDOs(exampleCommands);
        if (!CollectionUtils.isEmpty(examples)) {
            for (ExampleSentenceDO example : examples) {
                example.setVocId(vocId);
                exampleSentenceMapper.insert(example);
            }
        }

        // 5. 保存书籍-章节-单词关系（完善逻辑）
        String bookInfos = dto.getBookInfos();
        if (bookInfos == null || bookInfos.trim().isEmpty()) {
            return; // 无关系信息则跳过
        }

        // 5.1 解析书籍和章节信息（格式：书籍1#章节1,书籍2#章节2）
        String[] bookChapterItems = bookInfos.split(",");
        for (String item : bookChapterItems) {
            item = item.trim();
            if (item.isEmpty()) {
                continue;
            }

            String[] parts = item.split("#", 2);
            if (parts.length < 2) {
                continue; // 跳过格式错误的记录（无章节）
            }
            String bookName = parts[0].trim();
            String chapterName = parts[1].trim();

            // 5.2 查询书籍ID
            Long bookId = getBookIdByName(bookName);
            if (bookId == null) {
                throw new BusinessException(ResultCodeEnum.BOOK_OFFLINE);
            }

            // 5.3 查询章节ID
            Long chapterId = getChapterIdByBookAndName(bookId, chapterName);
            if (chapterId == null) {
                throw new BusinessException(ResultCodeEnum.CHAPTER_NOT_EXISTED);
            }

            // 5.4 保存单词-书籍-章节关系
            VocChapBookDO relation = new VocChapBookDO();
            relation.setVocId(vocId);
            relation.setBookId(bookId);
            relation.setChapterId(chapterId);
            relation.setIsDeleted(0); // 未删除

            // 5.5 检查关系是否已存在（避免重复）
            LambdaQueryWrapper<VocChapBookDO> relQuery = new LambdaQueryWrapper<>();
            relQuery.eq(VocChapBookDO::getVocId, vocId)
                    .eq(VocChapBookDO::getBookId, bookId)
                    .eq(VocChapBookDO::getChapterId, chapterId);

            VocChapBookDO existingRel = vocChapBookMapper.selectOne(relQuery);
            if (existingRel == null) {
                vocChapBookMapper.insert(relation);
                // 5.6 更新书籍和章节的单词数量
                updateVocCount(bookId, chapterId, 1); // +1
            } else if (existingRel.getIsDeleted() == 1) {
                // 恢复逻辑删除的关系
                existingRel.setIsDeleted(0);
                vocChapBookMapper.updateById(existingRel);
                updateVocCount(bookId, chapterId, 1); // +1
            }
        }
    }

    // 辅助方法
    /**
     * 根据书籍名称查询书籍ID
     */
    private Long getBookIdByName(String bookName) {
        LambdaQueryWrapper<VocBookDO> query = new LambdaQueryWrapper<>();
        query.eq(VocBookDO::getName, bookName)
                .eq(VocBookDO::getIsDeleted, 0)
                .select(VocBookDO::getId);
        VocBookDO book = vocBookMapper.selectOne(query);
        return book != null ? book.getId() : null;
    }

    /**
     * 根据书籍ID和章节名称查询章节ID
     */
    private Long getChapterIdByBookAndName(Long bookId, String chapterName) {
        LambdaQueryWrapper<VocChapterDO> query = new LambdaQueryWrapper<>();
        query.eq(VocChapterDO::getBookId, bookId)
                .eq(VocChapterDO::getChapterName, chapterName)
                .eq(VocChapterDO::getIsDeleted, 0)
                .select(VocChapterDO::getId);
        VocChapterDO chapter = vocChapterMapper.selectOne(query);
        return chapter != null ? chapter.getId() : null;
    }

    /**
     * 更新书籍和章节的单词数量
     * @param bookId 书籍ID
     * @param chapterId 章节ID
     * @param count 变动数量（+1或-1）
     */
    private void updateVocCount(Long bookId, Long chapterId, int count) {
        // 更新书籍单词数
        LambdaUpdateWrapper<VocBookDO> bookUpdate = new LambdaUpdateWrapper<>();
        bookUpdate.eq(VocBookDO::getId, bookId)
                .setSql("voc_count = voc_count + " + count);
        vocBookMapper.update(null, bookUpdate);

        // 更新章节单词数
        LambdaUpdateWrapper<VocChapterDO> chapterUpdate = new LambdaUpdateWrapper<>();
        chapterUpdate.eq(VocChapterDO::getId, chapterId)
                .setSql("voc_count = voc_count + " + count);
        vocChapterMapper.update(null, chapterUpdate);
    }

    private List<VocMeaningCommand> parseMeaningCommands(String interpretations) {
        List<VocMeaningCommand> commands = new ArrayList<>();
        if (interpretations == null || interpretations.isEmpty()) {
            return commands;
        }

        // 解析释义（格式：词性. 释义内容）
        String[] lines = interpretations.split("\\r?\\n");
        for (String line : lines) {
            if (line.trim().isEmpty()) {
                continue;
            }

            VocMeaningCommand command = new VocMeaningCommand();
            String[] parts = line.split("\\.", 2);
            if (parts.length >= 2) {
                command.setType(parts[0].trim());
                command.setInterContent(parts[1].trim());
            } else {
                command.setInterContent(parts[0].trim());
            }
            commands.add(command);
        }
        return commands;
    }

    private List<ExampleSentenceCommand> parseExampleCommands(String exampleSentences) {
        List<ExampleSentenceCommand> commands = new ArrayList<>();
        if (exampleSentences == null || exampleSentences.isEmpty()) {
            return commands;
        }

        // 解析例句（格式：英文例句\n中文翻译）
        String[] lines = exampleSentences.split("\\r?\\n");
        for (int i = 0; i < lines.length - 1; i += 2) {
            ExampleSentenceCommand command = new ExampleSentenceCommand();
            command.setContent(lines[i].trim());
            command.setMeaning(lines[i + 1].trim());
            commands.add(command);
        }
        return commands;
    }

    /**
     * 删除单词音频文件
     *
     * @param id
     * @param type
     * @param name
     * @return
     */
    @Override
    @OperateLog(dataType = "单词", operateType = 2, description = "删除单词音频文件", logReturnValue = true)
    public DeleteVocFileOperateDTO deleteFile(Long id, String type, String name) {
        // 1. 查询单词信息
        VocDO vocDO = vocMapper.selectById(id);
        if (vocDO == null) {
            throw new BusinessException(ResultCodeEnum.VOC_NOT_EXISTED);
        }

        // 2. 获取要删除的音频文件URL
        String fileUrl = null;
        if ("us".equalsIgnoreCase(type)) {
            fileUrl = vocDO.getUsPronounciation();
        } else if ("uk".equalsIgnoreCase(type)) {
            fileUrl = vocDO.getUkPronounciation();
        } else {
            throw new BusinessException(ResultCodeEnum.PARAM_ERROR);
        }

        if (fileUrl == null || fileUrl.isEmpty()) {
            throw new BusinessException(ResultCodeEnum.PARAM_ERROR);
        }

        // 3. 从URL中提取文件名
        String fileName = (fileUrl.contains("/")) ? fileUrl.substring(fileUrl.lastIndexOf("/") + 1) : fileUrl;

        // 4. 删除OSS上的文件
        try {
            fileUploadService.deleteFile(fileName);
        } catch (Exception e) {
            // 记录日志但不抛出异常，因为可能文件已经不存在
            log.warn("删除OSS文件失败: {}", fileName, e);
        }

        // 5. 更新数据库中的音频字段
        LambdaUpdateWrapper<VocDO> updateWrapper = new LambdaUpdateWrapper<>();
        updateWrapper.eq(VocDO::getId, id);
        if ("us".equalsIgnoreCase(type)) {
            updateWrapper.set(VocDO::getUsPronounciation, null);
        } else if ("uk".equalsIgnoreCase(type)) {
            updateWrapper.set(VocDO::getUkPronounciation, null);
        }
        vocMapper.update(null, updateWrapper);

        // 6. 构造返回结果
        DeleteVocFileOperateDTO dto = new DeleteVocFileOperateDTO();
        dto.setVocId(id);
        dto.setContent(vocDO.getContent());
        dto.setType(type);
        dto.setFileName(fileName);

        return dto;
    }

    /**
     * 删除单词与章节、书籍的关系
     *
     * @param id
     * @return
     */
    @Override
    @Transactional
    @OperateLog(dataType = "单词", operateType = 2, description = "删除单词与章节关系", logReturnValue = true)
    public DeleteVocRelationOperateDTO deleteVocRelation(Long id) {
        VocChapBookDO relation = vocChapBookMapper.selectById(id);
        if (relation == null)
            return null;
        vocChapBookMapper.deleteById(id);
        if (relation.getChapterId() != null) {
            vocChapterMapper.update(null, new LambdaUpdateWrapper<VocChapterDO>()
                    .eq(VocChapterDO::getId, relation.getChapterId())
                    .setSql("voc_count = voc_count - 1"));
        }
        vocBookMapper.update(null, new LambdaUpdateWrapper<VocBookDO>()
                .eq(VocBookDO::getId, relation.getBookId())
                .setSql("voc_count = voc_count - 1"));
        DeleteVocRelationOperateDTO dto = new DeleteVocRelationOperateDTO();
        dto.setId(id);
        return dto;
    }

    /**
     * 删除释义
     *
     * @param id
     * @return
     */
    @Override
    @Transactional
    @OperateLog(dataType = "单词", operateType = 2, description = "删除单词释义", logReturnValue = true)
    public DeleteInterpretationOperateDTO deleteInterpretation(Long id) {
        VocMeaningDO meaning = vocMeaningMapper.selectById(id);
        if (meaning == null)
            return null;
        vocMeaningMapper.deleteById(id);
        DeleteInterpretationOperateDTO dto = new DeleteInterpretationOperateDTO();
        dto.setId(id);
        return dto;
    }

    /**
     * 删除例句
     *
     * @param id
     * @return
     */
    @Override
    @Transactional
    @OperateLog(dataType = "单词", operateType = 2, description = "删除单词例句", logReturnValue = true)
    public DeleteVocExampleOperateDTO deleteExample(Long id) {
        ExampleSentenceDO example = exampleSentenceMapper.selectById(id);
        if (example == null)
            return null;
        exampleSentenceMapper.deleteById(id);
        DeleteVocExampleOperateDTO dto = new DeleteVocExampleOperateDTO();
        dto.setId(id);
        return dto;
    }

    // 根据id查询单词详细信息
    @Override
    public VocDTO getVocById(Long vocId) {
        VocDO vocDO = vocMapper.selectById(vocId);
        if (vocDO == null) {
            return null;
        }
        // 查询释义
        List<VocMeaningDO> meanings = vocMeaningMapper.selectList(
                new LambdaQueryWrapper<VocMeaningDO>().eq(VocMeaningDO::getVocId, vocId));
        vocDO.setInterpretations(meanings);
        // 查询例句
        List<ExampleSentenceDO> examples = exampleSentenceMapper.selectList(
                new LambdaQueryWrapper<ExampleSentenceDO>().eq(ExampleSentenceDO::getVocId, vocId));
        vocDO.setExamples(examples);
        // 转换为DTO
        return vocConverter.vocDO2DTO(vocDO);
    }
}
